//
//  DBHandler.m
//  5.27知识点UI18_SQLite
//
//  Created by dingcong on 16/5/27.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DBHandler.h"
#import <sqlite3.h>
#import "SelectionModel.h"

@implementation DBHandler
//创建一个sqlite3对象(全局变量, 别的方法也可以访问这个对象)
sqlite3 *db;
//单例
+ (instancetype)sharedDBHandle{
    static DBHandler *db = nil;
    if (db == nil)
    {
        db = [[DBHandler alloc]init];
    }
    return db;
    
}
//开启数据库
- (void)openDB{
    if (db)
    {
        NSLog(@"数据库已经打开");
        return;
    }
    
    
    
    NSString *path = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject]stringByAppendingPathComponent:@"Collection.db"];
    NSLog(@"%@", path);
    
    //核心API sqlite3
   int result =  sqlite3_open(path.UTF8String, &db);
    if (SQLITE_OK == result)
    {
        NSLog(@"数据库打开成功!!!");
    }
    else
    {
        NSLog(@"数据库打开失败, code:%d", result);
    }
    
    
}
//创建表格
- (void)createTable{
    
    
    NSString *sql = @"create table IF NOT EXISTS Collection(number INTEGER PRIMARY KEY AUTOINCREMENT,      subject TEXT, pic TEXT, mob_h5_url TEXT)";
    //核心API: sqlite_exec
    NSLog(@"%@", sql);
   int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK)
    {
        NSLog(@"创建成功!!!");
        
    }
    else
    {
        NSLog(@"创建失败!, code:%d", result);
    }
    
    
    
}

- (void)insertModel:(NewModel *)model{
    
    NSArray *arr = [[DBHandler sharedDBHandle] searchForName:model.subject];
    
    if (arr.count == 0) {
        
    
    [[DBHandler sharedDBHandle]searchForName:model.subject];
        NSString *SQL = [NSString stringWithFormat:@"INSERT into Collection(subject, pic, mob_h5_url) VALUES('%@', '%@', '%@')", model.subject, model.pic, model.mob_h5_url];
        NSLog(@"%@", SQL);
        //核心API
        int result =  sqlite3_exec(db, SQL.UTF8String, NULL, NULL, NULL);
    
    
        if (result == SQLITE_OK) {
            NSLog(@"添加数据完成!");
        } else {
            NSLog(@"添加数据失败.code:%d", result);
        }
    
    }
    
}

- (void)deleteStudentWithNumber:(NSInteger)number
{
    NSString *str = [NSString stringWithFormat:@"delete from Collection where number = '%ld'", number];
    
    //核心API
    
    int result = sqlite3_exec(db, str.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK)
    {
        NSLog(@"删除成功");
    } else {
        NSLog(@"删除失败, code:%d", result);
    }
  
}

- (NSArray <NewModel *> *)getData{
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Collection"];
    NSLog(@"%@", sql);
    NSMutableArray *mArr = [NSMutableArray array];
    
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK)
    {
        NSLog(@"准备取出.....");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //将匹配的行取出来
            const unsigned char *subject = sqlite3_column_text(stmt, 1);
            const unsigned char *mob_h5_url = sqlite3_column_text(stmt, 3);
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            NewModel *model = [[NewModel alloc]init];
            
            model.subject = [NSString stringWithUTF8String:(const char *)subject];
            
            model.mob_h5_url = [NSString stringWithUTF8String:(const char *)mob_h5_url];
            
            model.pic = [NSString stringWithUTF8String:(const char *)pic];
         
            [mArr addObject:model];
        }
    }
    
    else
    {
        NSLog(@"取出失败, code:%d", result);
        
    }
    
    
    return mArr;
    
    
    
   
    
    
    
    
}


- (NSArray <NewModel *> *)searchForName:(NSString *)subjects
{
    NSMutableArray *mArr = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM Collection WHERE subject = '%@'", subjects];
    
    
    //核心API: stmt
    sqlite3_stmt *stmt = nil;
   int result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK)
    {
        NSLog(@"准备查询.....");
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            //将匹配的行取出来
            const unsigned char *subject = sqlite3_column_text(stmt, 1);
            const unsigned char *mob_h5_url = sqlite3_column_text(stmt, 3);
            const unsigned char *pic = sqlite3_column_text(stmt, 2);
            NewModel *model = [[NewModel alloc]init];
            
            model.subject = [NSString stringWithUTF8String:(const char *)subject];
            
            model.mob_h5_url = [NSString stringWithUTF8String:(const char *)mob_h5_url];
            
            model.pic = [NSString stringWithUTF8String:(const char *)pic];
            [mArr addObject:model];
            
            
            
            [model release];
            
            
        }
    }
    
    else
    {
        NSLog(@"查询失败, code:%d", result);
        
    }
    
    
    return mArr;

}

- (void)dropTable{
    
    NSString *sql = @"Drop table Collection";
    
    //核心API
   int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK)
    {
        NSLog(@"删除表格成功!!");
        
    }
    else
    {
        NSLog(@"删除表格失败!!, %d", result);
        
    }
    
    
    
    
    
}

- (void)closeDBhandle{
    
    //核心API
   int result = sqlite3_close(db);
    if (result == SQLITE_OK)
    {
        db = nil;
        NSLog(@"数据库关闭成功!");
    }
    else
    {
        NSLog(@"数据库关闭失败!, %d",result );
    }
    
    
    
}
@end
